/**
 *    Copyright 2009-2017 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package org.apache.ibatis.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

/**一个非常实用的、用于操作数据库的工具类。该类对jdbc做了很好的封装，结合sql工具类，能够很方便的通过java代码执行sql语句并检索sql执行结果
 * @author Clinton Begin
 */
public class SqlRunner {

	public static final int NO_GENERATED_KEY = Integer.MIN_VALUE + 1001;

	private final Connection connection;
	private final TypeHandlerRegistry typeHandlerRegistry;
	private boolean useGeneratedKeySupport;

	public SqlRunner(Connection connection) {
		this.connection = connection;
		this.typeHandlerRegistry = new TypeHandlerRegistry();
	}

	public void setUseGeneratedKeySupport(boolean useGeneratedKeySupport) {
		this.useGeneratedKeySupport = useGeneratedKeySupport;
	}

	/**执行select语句，sql语句中可以使用占位符，如果sql中包含占位符，则可变参数用于为参数占位符赋值，该方法只返回一条记录。
	 * <br/>若查询结果不等于一，则抛出SQLException异常
	 * <br/>Executes a SELECT statement that returns one row.
	 * @param sql The SQL
	 * @param args The arguments to be set on the statement.
	 * @return The row expected.
	 * @throws SQLException If less or more than one row is returned
	 */
	public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
		List<Map<String, Object>> results = selectAll(sql, args);
		if (results.size() != 1) {
			throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
		}
		return results.get(0);
	}

	/**和selectOne作用相同，只不过可以返回多条记录，返回值是一个list对象，list中包含多个map对象，每个map对象对应数据库中的一行记录
	 * <br/>Executes a SELECT statement that returns multiple rows.
	 * @param sql The SQL
	 * @param args The arguments to be set on the statement.
	 * @return The list of rows expected.
	 * @throws SQLException If statement preparation or execution fails
	 */
	public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
		PreparedStatement ps = connection.prepareStatement(sql);
		try {
			// 为sql中的参数占位符赋值
			setParameters(ps, args);
			// 执行查询操作
			ResultSet rs = ps.executeQuery();
			// 将查询结果转换为list，其中list对象中的每个map对象对应数据库中的一行记录
			return getResults(rs);
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// ignore
			}
		}
	}

	/**插入一条记录
	 * <br/>Executes an INSERT statement.
	 * @param sql The SQL
	 * @param args The arguments to be set on the statement.
	 * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
	 * @throws SQLException If statement preparation or execution fails
	 */
	public int insert(String sql, Object... args) throws SQLException {
		PreparedStatement ps;
		if (useGeneratedKeySupport) {
			ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		} else {
			ps = connection.prepareStatement(sql);
		}

		try {
			setParameters(ps, args);
			ps.executeUpdate();
			if (useGeneratedKeySupport) {
				List<Map<String, Object>> keys = getResults(ps.getGeneratedKeys());
				if (keys.size() == 1) {
					Map<String, Object> key = keys.get(0);
					Iterator<Object> i = key.values().iterator();
					if (i.hasNext()) {
						Object genkey = i.next();
						if (genkey != null) {
							try {
								return Integer.parseInt(genkey.toString());
							} catch (NumberFormatException e) {
								// ignore, no numeric key support
							}
						}
					}
				}
			}
			return NO_GENERATED_KEY;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// ignore
			}
		}
	}

	/**更新若干条记录
	 * <br/>Executes an UPDATE statement.
	 * @param sql The SQL
	 * @param args The arguments to be set on the statement.
	 * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
	 * @throws SQLException If statement preparation or execution fails
	 */
	public int update(String sql, Object... args) throws SQLException {
		PreparedStatement ps = connection.prepareStatement(sql);
		try {
			setParameters(ps, args);
			return ps.executeUpdate();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// ignore
			}
		}
	}

	/**删除若干条记录
	 * <br/>Executes a DELETE statement.
	 * @param sql The SQL
	 * @param args The arguments to be set on the statement.
	 * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
	 * @throws SQLException If statement preparation or execution fails
	 */
	public int delete(String sql, Object... args) throws SQLException {
		return update(sql, args);
	}

	/**执行任意一条sql语句，最好为ddl语句
	 * <br/>Executes any string as a JDBC Statement. Good for DDL
	 * @param sql The SQL
	 * @throws SQLException If statement preparation or execution fails
	 */
	public void run(String sql) throws SQLException {
		Statement stmt = connection.createStatement();
		try {
			stmt.execute(sql);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				// ignore
			}
		}
	}

	/**关闭Connection对象
	 *
	 */
	public void closeConnection() {
		try {
			connection.close();
		} catch (SQLException e) {
			// ignore
		}
	}

	private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
		for (int i = 0, n = args.length; i < n; i++) {
			if (args[i] == null) {
				throw new SQLException("SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
			} else if (args[i] instanceof Null) {
				((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
			} else {
				// 根据参数类型获取对应的TypeHandler
				TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
				if (typeHandler == null) {
					throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
				} else {
					// 调用TypeHandler的setParameter方法为参数占位符赋值
					typeHandler.setParameter(ps, i + 1, args[i], null);
				}
			}
		}
	}

	private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			List<String> columns = new ArrayList<String>();
			List<TypeHandler<?>> typeHandlers = new ArrayList<TypeHandler<?>>();
			// 获取ResultSetMetaData对象，里面封装了结果集的元数据信息，包括所有的字段名称及列的数量等信息。通过ResultSetMetaData获取所有列名
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
				columns.add(rsmd.getColumnLabel(i + 1));
				try {
					// 获取jdbc类型，根据类型获取TypeHandler对象，将TypeHandler对象注册到变量名为typeHandlers的ArrayList对象中
					Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
					TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
					if (typeHandler == null) {
						typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
					}
					typeHandlers.add(typeHandler);
				} catch (Exception e) {
					typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
				}
			}
			// 遍历ResultSet对象，将其中的记录行转换为Map对象
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 0, n = columns.size(); i < n; i++) {
					String name = columns.get(i);
					TypeHandler<?> handler = typeHandlers.get(i);
					// 通过TypeHandler对象的getResult方法将jdbc类型转换为java类型，然后将ResultSet对象中的记录行转换为Map对象
					row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
				}
				list.add(row);
			}
			return list;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					// ignore
				}
			}
		}
	}

}
